package encaps.entities;

import encaps.utils.DatabaseUtil;
import encaps.utils.StringUtil;
import java.sql.*;
import java.util.*;
import javax.ejb.*;
import javax.naming.NamingException;
import javax.sql.DataSource;

public class Person implements EntityBean {

    public static final String TABLE_NAME = "Person";
    public static final String NAME = "name";
    public static final String BIOGRAPHY = "biography";
    public static final String PIC = "pic";
    public static final String ID = "id";
    private EntityContext context;
    private DataSource dataSource;
    private boolean isEdited;
    private Long id;
    private String name;
    private String photo;
    private String bio;

    public void setEntityContext(EntityContext aContext) {
        context = aContext;
        try {
            dataSource = DatabaseUtil.getDataSource();
            isEdited = false;
        } catch (NamingException e) {
            throw new EJBException("DataSource lookup error", e);
        }
    }

    public void unsetEntityContext() {
        context = null;
    }

    public Long ejbCreate(String name, String photo, String bio) throws CreateException {
        PreparedStatement insertValuesQuery = null;
        PreparedStatement getMovieTitles = null;
        Connection conn = null;
        try {
            this.photo = photo;
            this.name = name;
            this.bio = bio;
            conn = dataSource.getConnection();
            insertValuesQuery = conn.prepareStatement("INSERT INTO person(name, pic, biography) VALUES(?, ?, ?)", Statement.RETURN_GENERATED_KEYS);
            insertValuesQuery.setString(1, name);
            insertValuesQuery.setString(2, photo);
            insertValuesQuery.setString(3, bio);

            if (insertValuesQuery.executeUpdate() != 1) {
                throw new CreateException("Insertion error");
            }
            ResultSet generatedIdRS = insertValuesQuery.getGeneratedKeys();
            if (!generatedIdRS.next()) {
                throw new CreateException("Insertion error");
            }
            id = generatedIdRS.getLong(1);

            isEdited = false;
            return id;
        } catch (Exception ex) {
            throw new EJBException("Error in Person.ejbCreate method", ex);
        } finally {
            DatabaseUtil.closeConnection(conn, insertValuesQuery);
            DatabaseUtil.closeConnection(null, getMovieTitles);
        }

    }

    public void ejbPostCreate(String name, String photo, String bio) throws CreateException {
    }

    public void ejbActivate() {
        //doin' nothing
    }

    public void ejbPassivate() {
        //doin' nothing
    }

    public void ejbRemove() throws RemoveException {
        PreparedStatement ps = null;
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            ps = conn.prepareStatement("DELETE FROM movie_person WHERE person_id=?");
            ps.setLong(1, id);
            ps.executeUpdate();
            DatabaseUtil.closeConnection(null, ps);
            ps = conn.prepareStatement("DELETE FROM person WHERE id=?");
            ps.setLong(1, id);
            if (ps.executeUpdate() < 1) {
                throw new RemoveException("Deletion error. Id=" + id);
            }
            isEdited = false;
        } catch (SQLException ex) {
            throw new EJBException("Error in Person.ejbRemove method", ex);
        } finally {
            DatabaseUtil.closeConnection(conn, ps);
        }
    }

    public void ejbLoad() {
        id = (Long) context.getPrimaryKey();
        PreparedStatement ps = null;
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            ps = conn.prepareStatement("SELECT name, biography, pic FROM person WHERE id=?");
            ps.setLong(1, id);
            ResultSet rs = ps.executeQuery();
            if (!rs.next()) {
                throw new NoSuchEntityException("No person found by id = " + id);
            }
            photo = rs.getString(PIC);
            name = rs.getString(NAME);
            bio = rs.getString(BIOGRAPHY);
            isEdited = false;
        } catch (SQLException ex) {
            throw new EJBException("Error in ejbLoad method", ex);
        } finally {
            DatabaseUtil.closeConnection(conn, ps);
        }
    }

    public void ejbStore() {
        if (!isEdited) {
            return;
        }
        PreparedStatement ps = null;
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            ps = conn.prepareStatement("UPDATE person SET name=?, pic=?, biography=? WHERE id=?");
            ps.setString(1, name);
            ps.setString(2, photo);
            ps.setString(3, bio);
            ps.setLong(4, id);
            if (ps.executeUpdate() < 1) {
                throw new NoSuchEntityException("No person found by id = " + id);
            }
            isEdited = false;
        } catch (SQLException ex) {
            throw new EJBException("Error in Person.ejbStore method", ex);
        } finally {
            DatabaseUtil.closeConnection(conn, ps);
        }
    }

    public java.lang.Long ejbFindByPrimaryKey(Long key) throws ObjectNotFoundException {
        PreparedStatement ps = null;
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            ps = conn.prepareStatement("SELECT id FROM person WHERE id=?;");
            ps.setLong(1, key);
            ResultSet rs = ps.executeQuery();
            if (!rs.next()) {
                throw new ObjectNotFoundException("No person found by primary key = " + key);
            }
            return key;
        } catch (SQLException ex) {
            throw new EJBException("Error in Person.ejbFindByPrimaryKey method", ex);
        } finally {
            DatabaseUtil.closeConnection(conn, ps);
        }
    }

    public Collection ejbFindByName(String name) throws FinderException {
        HashMap criteria = new HashMap();
        if (!StringUtil.isEmpty(name)) {
            criteria.put(NAME, name);
        }
        if (criteria.isEmpty()) {
            throw new FinderException("Person search criteria are empty");
        }

        PreparedStatement ps = null;
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            String searchQuery = StringUtil.constructSelectQuery(TABLE_NAME, "id", criteria);
            ps = conn.prepareStatement(searchQuery);
            Collection keys = new LinkedList();
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                keys.add(rs.getLong(1));
            }
            return keys;
        } catch (SQLException ex) {
            throw new EJBException("Error in Person.ejbFindByName method", ex);
        } finally {
            DatabaseUtil.closeConnection(conn, ps);
        }
    }

    public Integer ejbHomeGetPersonsCount(String name) throws FinderException {
        PreparedStatement ps = null;
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            ps = conn.prepareStatement("SELECT Count(id) FROM Person WHERE name LIKE ?");
            String nameLike = StringUtil.getLikePattern(name);
            ps.setString(1, nameLike);
            ResultSet rs = ps.executeQuery();
            rs.first();
            return rs.getInt(1);
        } catch (SQLException ex) {
            throw new EJBException("Error in Movie.getPersonsCount method", ex);
        } finally {
            DatabaseUtil.closeConnection(conn, ps);
        }
    }

    public Integer ejbHomeGetPersonsCount(String name, String bio, String movieTitle) throws FinderException {
        PreparedStatement ps = null;
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            String queryString = "SELECT count(DISTINCT person.id) FROM "
                    + "(person LEFT JOIN movie_person ON person.id = movie_person.person_id) "
                    + "LEFT JOIN movie ON movie.id = movie_person.movie_id WHERE name LIKE ? "
                    + "AND IFNULL(biography,'') LIKE ? AND IFNULL(title,'') LIKE ?";
            ps = conn.prepareStatement(queryString);
            ps.setString(1, StringUtil.getLikePattern(name));
            ps.setString(2, StringUtil.getLikePattern(bio));
            ps.setString(3, StringUtil.getLikePattern(movieTitle));
            ResultSet rs = ps.executeQuery();
            rs.first();
            return rs.getInt(1);
        } catch (SQLException ex) {
            throw new EJBException("Error in Movie.getPersonsCount method", ex);
        } finally {
            DatabaseUtil.closeConnection(conn, ps);
        }
    }

    public Collection ejbFindAll() {
        PreparedStatement ps = null;
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            ps = conn.prepareStatement("SELECT id FROM Person");
            Collection keys = new LinkedList();
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                keys.add(rs.getLong(1));
            }
            return keys;
        } catch (SQLException ex) {
            throw new EJBException("Error in Person.ejbFindAll method", ex);
        } finally {
            DatabaseUtil.closeConnection(conn, ps);
        }
    }

    public HashMap ejbHomeGetAll() {
        PreparedStatement ps = null;
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            ps = conn.prepareStatement("SELECT id, name FROM Person");
            HashMap idnames = new HashMap();
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                idnames.put(rs.getLong(1), rs.getString(2));
            }
            return idnames;
        } catch (SQLException ex) {
            throw new EJBException("Error in Person.ejbHomeGetAll method", ex);
        } finally {
            DatabaseUtil.closeConnection(conn, ps);
        }

    }

    public Collection ejbFindPaged(String name, String orderColumn,
            OrderType orderType, Integer from, Integer count) {
        PreparedStatement ps = null;
        Connection conn = null;
        try {
            conn = dataSource.getConnection();

            String queryPattern = "SELECT id FROM Person WHERE name LIKE ? %s LIMIT ?, ?";
            String orderClause;
            if (!StringUtil.isEmpty(orderColumn)) {
                StringBuilder sb = new StringBuilder();
                sb.append("ORDER BY ");
                sb.append(orderColumn);
                if (orderType.toString() != null) {
                    sb.append(" ");
                    sb.append(orderType.toString());
                }
                sb.append(" ");
                orderClause = sb.toString();
            } else {
                orderClause = "";
            }
            String queryString = String.format(queryPattern, orderClause);

            ps = conn.prepareStatement(queryString);
            String nameLike = StringUtil.getLikePattern(name);
            ps.setString(1, nameLike);
            ps.setInt(2, from);
            ps.setInt(3, count);
            Collection keys = new LinkedList();
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                keys.add(rs.getLong(1));
            }
            return keys;
        } catch (SQLException ex) {
            throw new EJBException("Error in Person.ejbFindAllPaged method", ex);
        } finally {
            DatabaseUtil.closeConnection(conn, ps);
        }
    }

    public Collection ejbFindByParametersPaged(String name, String bio,
            String movieTitle, String orderColumn, OrderType orderType,
            Integer from, Integer count) {
        PreparedStatement ps = null;
        Connection conn = null;
        try {
            conn = dataSource.getConnection();

            String queryPattern = "SELECT DISTINCT person.id FROM "
                    + "(person LEFT JOIN movie_person ON person.id = movie_person.person_id) "
                    + "LEFT JOIN movie ON movie.id = movie_person.movie_id WHERE name LIKE ? "
                    + "AND IFNULL(biography,'') LIKE ? AND IFNULL(title,'') LIKE ? %s LIMIT ?, ?";
            String orderClause;
            if (!StringUtil.isEmpty(orderColumn)) {
                StringBuilder sb = new StringBuilder();
                sb.append("ORDER BY ");
                sb.append(orderColumn);
                if (orderType.toString() != null) {
                    sb.append(" ");
                    sb.append(orderType.toString());
                }
                sb.append(" ");
                orderClause = sb.toString();
            } else {
                orderClause = "";
            }
            String queryString = String.format(queryPattern, orderClause);

            ps = conn.prepareStatement(queryString);
            ps.setString(1, StringUtil.getLikePattern(name));
            ps.setString(2, StringUtil.getLikePattern(bio));
            ps.setString(3, StringUtil.getLikePattern(movieTitle));
            ps.setInt(4, from);
            ps.setInt(5, count);
            Collection keys = new LinkedList();
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                keys.add(rs.getLong(1));
            }//System.out.println(keys.size());
            return keys;
        } catch (SQLException ex) {
            throw new EJBException("Error in Person.ejbFindAllPaged method", ex);
        } finally {
            DatabaseUtil.closeConnection(conn, ps);
        }
    }

    public Set<MoviePersonInfo> getMovies() {
        PreparedStatement ps = null;
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            ps = conn.prepareStatement("SELECT movie_id, title, type FROM (movie JOIN movie_person ON movie.id = movie_person.movie_id) WHERE movie_person.person_id = ?");
            ps.setLong(1, id);
            ResultSet rs = ps.executeQuery();
            Set<MoviePersonInfo> movies = new HashSet<MoviePersonInfo>();
            while (rs.next()) {
                MoviePersonInfo mpi = new MoviePersonInfo();
                mpi.setId(rs.getLong("movie_id"));
                mpi.setName(rs.getString("title"));
                mpi.setType(rs.getString("type"));
                movies.add(mpi);
            }
            return movies;
        } catch (SQLException e) {
            throw new EJBException("Error in select", e);
        } finally {
            DatabaseUtil.closeConnection(conn, ps);
        }
    }

    public Long getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
        isEdited = true;
    }

    public String getPhoto() {
        return photo;
    }

    public void setPhoto(String photo) {
        this.photo = photo;
        isEdited = true;
    }

    public String getBio() {
        return bio;
    }

    public void setBio(String bio) {
        this.bio = bio;
        isEdited = true;
    }

    public String stringRepresentation() {
        StringBuilder sb = new StringBuilder();
        sb.append(getClass().getName());
        sb.append(": [");
        sb.append("id=");
        sb.append(id);
        sb.append(", name=");
        sb.append(name);
        sb.append(", bio=");
        sb.append(bio);
        sb.append(", photo=");
        sb.append(photo);
        sb.append("]");
        return sb.toString();
    }
}
